--HEADER Separate Schema of Market Data from Trading Data 
CREATE  schema [MarketData] AUTHORIZATION [dbo]
GO
PRINT N'schema'
GO
DROP TRIGGER [TradingData].[IT_TimeSerie]
GO
PRINT N'DROP TRIGGER [MarketData].[IT_TimeSerie]'
GO
DROP VIEW [TradingData].[SecurityView]
GO
PRINT N'DROP VIEW SecurityView'
GO

DROP VIEW [TradingData].[UsedSecurities]
GO
PRINT N'DROP VIEW UsedSecurities'
GO

DROP VIEW [TradingData].[JoinedTrade]
GO
PRINT N'DROP VIEW JoinedTrade'
GO

DROP PROCEDURE [TradingData].[UpdateParentId]
GO
PRINT N'DROP PROCEDURE UpdateParentId'
GO
--#####################################################################

PRINT N'DROP CONSTRAINTS ---------------------------------------------'
GO
ALTER TABLE [iComply].[IncludedEventSecurityClass]
DROP CONSTRAINT FK_IncludedEventSecurityClass_SecurityClass
GO
PRINT N'FK_IncludedEventSecurityClass_SecurityClass'

GO
ALTER TABLE [TradingData].[Security]
DROP CONSTRAINT FK_Security_Country
GO
PRINT N'FK_IncludedEventSecurityClass_SecurityClass'

GO
ALTER TABLE [TradingData].[Security]
DROP CONSTRAINT FK_Security_Currency
GO
PRINT N'FK_Security_Currency'

GO
ALTER TABLE [TradingData].[Security]
DROP CONSTRAINT FK_Security_EconomicSector
GO
PRINT N'FK_Security_EconomicSector'

GO
ALTER TABLE [TradingData].[Security]
DROP CONSTRAINT FK_Security_SecurityClass
GO
PRINT N'FK_Security_SecurityClass'

GO
ALTER TABLE [TradingData].[FxSerie]
DROP CONSTRAINT FK_FxSerie_Currency
GO
PRINT N'FK_FxSerie_Currency'

GO
ALTER TABLE [TradingData].[FundBookEntity]
DROP CONSTRAINT FK_FundBookEntity_Currency
GO
PRINT N'FK_FundBookEntity_Currency'

GO
ALTER TABLE [TradingData].[Trade]
DROP CONSTRAINT FK_Trade_Currency
GO
PRINT N'FK_Trade_Currency'

GO
ALTER TABLE [TradingData].[Trade]
DROP CONSTRAINT FK_Trade_Security
GO
PRINT N'FK_Trade_Security'

GO
ALTER TABLE [TradingData].[TradeConsistencyCheck]
DROP CONSTRAINT FK_TradeConsistencyCheck_Security
GO
PRINT N'FK_TradeConsistencyCheck_Security'

GO
ALTER TABLE [TradingData].[TimeSerie]
DROP CONSTRAINT FK_TimeSerie_Security
GO
PRINT N'FK_TimeSerie_Security'

GO
ALTER TABLE [DataConsistency].[SuspiciousExposure]
DROP CONSTRAINT FK_Exposure_Security
GO
PRINT N'FK_Exposure_Security'

GO
ALTER TABLE [TradingData].[BenchmarkList]
DROP CONSTRAINT FK_BenchmarkList_Security
GO
PRINT N'FK_BenchmarkList_Security'

GO
ALTER TABLE [TradingData].[BookSecurityToBenchmark]
DROP CONSTRAINT FK_BookSecurityToBenchmark_Security_Security
GO
PRINT N'FK_BookSecurityToBenchmark_Security_Security'

GO
ALTER TABLE [iComply].[ComplianceEvent]
DROP CONSTRAINT FK_ComplianceEvent_Security
GO
PRINT N'FK_ComplianceEvent_Security'

GO
ALTER TABLE [TradingData].[ExcessTimeSerie]
DROP CONSTRAINT FK_ExcessTimeSerie_Security
GO
PRINT N'FK_ExcessTimeSerie_Security'

GO
ALTER TABLE [TradingData].[OriginalPosition]
DROP CONSTRAINT FK_OriginalPosition_Security
GO
PRINT N'FK_OriginalPosition_Security'
GO
--######################################################################
PRINT N'UPDATE SCHEMA ---------------------------'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[TimeSerie]
GO
PRINT N'TimeSerie'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[SecurityClass]
GO
PRINT N'SecurityClass'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[Security]
GO
PRINT N'Security'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[FxSerie]
GO
PRINT N'FxSerie'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[EconomicSector]
GO
PRINT N'EconomicSector'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[Currency]
GO
PRINT N'Currency'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[Country]
GO
PRINT N'Country'
GO
ALTER SCHEMA [MarketData] TRANSFER [TradingData].[BenchmarkList]
GO
PRINT N'BenchmarkList'
GO
--######################################################################
PRINT N'-----------------------------------------------------'
GO
PRINT N'CREATE FK-----------------'
GO

--FK_Security_Country
SET ANSI_PADDING OFF
GO

ALTER TABLE [MarketData].[Security]  WITH NOCHECK ADD  CONSTRAINT [FK_Security_Country] FOREIGN KEY([CountryId])
REFERENCES [MarketData].[Country] ([Id])
GO

ALTER TABLE [MarketData].[Security] CHECK CONSTRAINT [FK_Security_Country]
GO

PRINT N'FK_Security_Country'
GO

--FK_Security_Currenc
ALTER TABLE [MarketData].[Security]  WITH NOCHECK ADD  CONSTRAINT [FK_Security_Currency] FOREIGN KEY([CurrencyId])
REFERENCES [MarketData].[Currency] ([Id])
GO

ALTER TABLE [MarketData].[Security] CHECK CONSTRAINT [FK_Security_Currency]
GO
PRINT N'FK_Security_Currency'
GO

--FK_Security_EconomicSector
ALTER TABLE [MarketData].[Security]  WITH NOCHECK ADD  CONSTRAINT [FK_Security_EconomicSector] FOREIGN KEY([EconomicSectorId])
REFERENCES [MarketData].[EconomicSector] ([Id])
GO

ALTER TABLE [MarketData].[Security] CHECK CONSTRAINT [FK_Security_EconomicSector]
GO
PRINT N'FK_Security_EconomicSector'
GO

--FK_Security_SecurityClass
ALTER TABLE [MarketData].[Security]  WITH NOCHECK ADD  CONSTRAINT [FK_Security_SecurityClass] FOREIGN KEY([ClassId])
REFERENCES [MarketData].[SecurityClass] ([Id])
GO

ALTER TABLE [MarketData].[Security] CHECK CONSTRAINT [FK_Security_SecurityClass]
GO
PRINT N'FK_Security_SecurityClass'
GO

--FK_IncludedEventSecurityClass_SecurityClass
ALTER TABLE [iComply].[IncludedEventSecurityClass]  WITH CHECK ADD  CONSTRAINT [FK_IncludedEventSecurityClass_SecurityClass] FOREIGN KEY([SecurityClassId])
REFERENCES [MarketData].[SecurityClass] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [iComply].[IncludedEventSecurityClass] CHECK CONSTRAINT [FK_IncludedEventSecurityClass_SecurityClass]
GO
PRINT N'FK_IncludedEventSecurityClass_SecurityClass'
GO

--FK_FxSerie_Currency
GO
ALTER TABLE [MarketData].[FxSerie]  WITH CHECK ADD  CONSTRAINT [FK_FxSerie_Currency] FOREIGN KEY([CurrencyId])
REFERENCES [MarketData].[Currency] ([Id])
GO

ALTER TABLE [MarketData].[FxSerie] CHECK CONSTRAINT [FK_FxSerie_Currency]
GO
PRINT N'FK_FxSerie_Currency'
GO

--FK_FundBookEntity_Currency
GO
ALTER TABLE [TradingData].[FundBookEntity]  WITH CHECK ADD  CONSTRAINT [FK_FundBookEntity_Currency] FOREIGN KEY([CurrencyId])
REFERENCES [MarketData].[Currency] ([Id])
GO

ALTER TABLE [TradingData].[FundBookEntity] CHECK CONSTRAINT [FK_FundBookEntity_Currency]
GO
PRINT N'FK_FundBookEntity_Currency'
GO

--FK_Trade_Currenc
GO
ALTER TABLE [TradingData].[Trade]  WITH CHECK ADD  CONSTRAINT [FK_Trade_Currency] FOREIGN KEY([CurrencyId])
REFERENCES [MarketData].[Currency] ([Id])
GO

ALTER TABLE [TradingData].[Trade] CHECK CONSTRAINT [FK_Trade_Currency]
GO
PRINT N'FK_Trade_Currency'
GO

--FK_Trade_Security
GO
ALTER TABLE [TradingData].[Trade]  WITH CHECK ADD  CONSTRAINT [FK_Trade_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [TradingData].[Trade] CHECK CONSTRAINT [FK_Trade_Security]
GO
PRINT N'FK_Trade_Security'
GO

--FK_TradeConsistencyCheck_Security
GO

ALTER TABLE [TradingData].[TradeConsistencyCheck]  WITH CHECK ADD  CONSTRAINT [FK_TradeConsistencyCheck_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [TradingData].[TradeConsistencyCheck] CHECK CONSTRAINT [FK_TradeConsistencyCheck_Security]
GO
PRINT N'FK_TradeConsistencyCheck_Security'
GO

--FK_TimeSerie_Security
GO
ALTER TABLE [MarketData].[TimeSerie]  WITH CHECK ADD  CONSTRAINT [FK_TimeSerie_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [MarketData].[TimeSerie] CHECK CONSTRAINT [FK_TimeSerie_Security]
GO
PRINT N'FK_TimeSerie_Security'
GO

--FK_Exposure_Security
GO
ALTER TABLE [DataConsistency].[SuspiciousExposure]  WITH CHECK ADD  CONSTRAINT [FK_Exposure_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [DataConsistency].[SuspiciousExposure] CHECK CONSTRAINT [FK_Exposure_Security]
GO
PRINT N'FK_Exposure_Security'
GO

--FK_BenchmarkList_Security
GO
ALTER TABLE [MarketData].[BenchmarkList]  WITH CHECK ADD  CONSTRAINT [FK_BenchmarkList_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [MarketData].[BenchmarkList] CHECK CONSTRAINT [FK_BenchmarkList_Security]
GO
PRINT N'[FK_BenchmarkList_Security]'
GO

--FK_BookSecurityToBenchmark_Security_Security
GO
ALTER TABLE [TradingData].[BookSecurityToBenchmark]  WITH CHECK ADD  CONSTRAINT [FK_BookSecurityToBenchmark_Security_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [TradingData].[BookSecurityToBenchmark] CHECK CONSTRAINT [FK_BookSecurityToBenchmark_Security_Security]
GO
PRINT N'[FK_BookSecurityToBenchmark_Security_Security]'
GO

--FK_ComplianceEvent_Security
GO
ALTER TABLE [iComply].[ComplianceEvent]  WITH CHECK ADD  CONSTRAINT [FK_ComplianceEvent_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [iComply].[ComplianceEvent] CHECK CONSTRAINT [FK_ComplianceEvent_Security]
GO
PRINT N'FK_ComplianceEvent_Security'
GO

--FK_ExcessTimeSerie_Security
GO
ALTER TABLE [TradingData].[ExcessTimeSerie]  WITH CHECK ADD  CONSTRAINT [FK_ExcessTimeSerie_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [TradingData].[ExcessTimeSerie] CHECK CONSTRAINT [FK_ExcessTimeSerie_Security]
GO
PRINT N'FK_ExcessTimeSerie_Security'
GO

--FK_OriginalPosition_Security
GO
ALTER TABLE [TradingData].[OriginalPosition]  WITH CHECK ADD  CONSTRAINT [FK_OriginalPosition_Security] FOREIGN KEY([SecurityId])
REFERENCES [MarketData].[Security] ([Id])
GO

ALTER TABLE [TradingData].[OriginalPosition] CHECK CONSTRAINT [FK_OriginalPosition_Security]
GO
PRINT N'FK_OriginalPosition_Security'
GO
--########################################################################################################################################################
PRINT N'CREATE TRIGGER [MarketData].[IT_TimeSerie] ON [MarketData].[TimeSerie] START'
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [MarketData].[IT_TimeSerie] ON [MarketData].[TimeSerie] INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON;

UPDATE T
SET 
    T.[Date]  =i.[Date],               
    T.[Open]= i.[Open],    
    T.[AdjustedOpen]=i.[AdjustedOpen],    
    T.[Close]=i.[Close],               
    T.[AdjustedClose]=i.[AdjustedClose],    
    T.[High]=i.[High],
    T.[AdjustedHigh]=i.[AdjustedHigh],
    T.[AdjustedLow]= i.[AdjustedLow],  
    T.[Low]=i.[Low],
    T.[StockReturn]=i.[StockReturn],
    T.[Bbg20DayMedianVolume]=i.[Bbg20DayMedianVolume],
    T.[Bbg90DayVolatility] =i.[Bbg90DayVolatility],
    T.[DayVolume] =i.[DayVolume],
    T.[AdjustedDayVolume]=i.[AdjustedDayVolume],
    T.[PriceToBook]=i.[PriceToBook],
    T.[PriceToCashflow]=i.[PriceToCashflow],
    T.[PriceToEarnings]=i.[PriceToEarnings],
    T.[DividendYield]=i.[DividendYield],
    T.[MarketCap]=i.[MarketCap]
FROM  [MarketData].[TimeSerie] T JOIN inserted i
ON T.SecurityId = i.SecurityId and T.[Date] = i.[Date]

INSERT [MarketData].[TimeSerie](
[SecurityId],
[Date],
[AdjustedOpen],
[AdjustedClose],
[AdjustedHigh],
[AdjustedLow],
[AdjustedDayVolume],
[Open],
[Close],
[High],
[Low],
[DayVolume],
[StockReturn],
[Bbg20DayMedianVolume],
[Bbg90DayVolatility],
[PriceToBook],
[PriceToCashflow],
[PriceToEarnings],
[DividendYield],
[MarketCap])
SELECT	
	i.[SecurityId],
	i.[Date],
	i.[AdjustedOpen],
	i.[AdjustedClose],
	i.[AdjustedHigh],
	i.[AdjustedLow],
	i.[AdjustedDayVolume],
	i.[Open],
	i.[Close],
	i.[High],
	i.[Low],
	i.[DayVolume],
	i.[StockReturn],
	i.[Bbg20DayMedianVolume],
	i.[Bbg90DayVolatility],
	i.[PriceToBook],
	i.[PriceToCashflow],
	i.[PriceToEarnings],
	i.[DividendYield],
	i.[MarketCap]		
FROM inserted i
WHERE NOT EXISTS(SELECT 1 FROM  [MarketData].[TimeSerie] T WHERE T.SecurityId = i.SecurityId and T.[Date] = i.[Date])
END

GO
PRINT N'CREATE TRIGGER [MarketData].[IT_TimeSerie] ON [MarketData].[TimeSerie] FINISH'
GO
--########################################################################################################################################################
PRINT N'-------------------------------------'
GO
PRINT N'CREATE StoredProcedure [TradingData].[UpdateParentId] START' 
GO
/****** Object:  StoredProcedure [TradingData].[UpdateParentId]    Script Date: 5/21/2014 9:56:45 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [TradingData].[UpdateParentId]
AS
SET NOCOUNT ON;
DECLARE @SecurityCount int;
select @SecurityCount =  count(id) from [MarketData].[Security] where ParentId = 0 or ParentId is NULL;
PRINT @SecurityCount

Declare @TempEmpTbl Table (RowId int identity, id int, Isin varchar(20),Ric varchar(20)
							,Mic varchar(20),Sedol varchar(20),CustomIdentificator varchar(20), ParentIdentify varchar(20),ParentIdentifyType varchar(20));
Declare @TempEmpTblId Table (Id int );

Insert into @TempEmpTbl Select id,Isin,Ric,Mic,Sedol,CustomIdentificator,ParentIdentify, ParentIdentifyType from [MarketData].[Security] where (ParentId = 0) and ParentIdentify<>'';


Declare @TempEmpCount Int = (Select Count(RowId) From @TempEmpTbl);
Print @TempEmpCount
Declare @MinCount Int = 1;
Declare @Id int;
Declare @ParentId int;
Declare @ParentIdentifyType varchar(20);
Declare @ParentIdentify varchar(20);
Declare @sql nvarchar(1000);
DECLARE @ParmDefinition nvarchar(500);

While(@TempEmpCount >= @MinCount)
Begin
	
	select @Id=id,@ParentIdentify=ParentIdentify,@ParentIdentifyType=ParentIdentifyType From @TempEmpTbl Where RowId = @MinCount

	Set @sql = N'select  @retvalOUT =  id from  [MarketData].[Security] where '+@ParentIdentifyType+N' = '''+@ParentIdentify+N''''
	SET @ParmDefinition = N'@retvalOUT int OUTPUT';
	EXEC sp_executesql @sql, @ParmDefinition, @retvalOUT=@ParentId OUTPUT;
	
	update [MarketData].[Security] set ParentId =@ParentId where id = @Id and @ParentId!= @Id
	update [MarketData].[Security] set ParentId =NULL where id = @Id and @ParentId= @Id
	select @ParentId =0
    Set @MinCount = @MinCount + 1;
End

GO
PRINT N'CREATE StoredProcedure [TradingData].[UpdateParentId] FINISH' 
GO

--########################################################################################################################################################
PRINT N'CREATE View [TradingData].[JoinedTrade] START' 
GO
/****** Object:  View [TradingData].[JoinedTrade]    Script Date: 5/21/2014 10:18:52 AM ******/
SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [TradingData].[JoinedTrade]
WITH SCHEMABINDING
AS
SELECT     t.FundBookEntityId, t.SecurityId, t.Id, t.Quantity, t.Date, t.CurrencyId, t.Price, t.FxRate, bs2b.BenchmarkId
FROM         TradingData.Trade AS t INNER JOIN
                      TradingData.BookSecurityToBenchmark AS bs2b ON t.FundBookEntityId = bs2b.FundBookEntityId AND t.SecurityId = bs2b.SecurityId INNER JOIN
                         MarketData.Security AS s ON t.SecurityId = s.Id INNER JOIN
                         MarketData.SecurityClass AS sc ON s.ClassId = sc.Id
WHERE        (sc.Include = 1)
GO

PRINT N'CREATE View [TradingData].[JoinedTrade] FINISH'
GO 
----##########################################################################################################################################################################
PRINT N'CREATE View [MarketData].[SecurityView] START'
GO
/****** Object:  View [MarketData].[SecurityView]    Script Date: 5/21/2014 11:22:22 AM ******/
SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [MarketData].[SecurityView]
WITH SCHEMABINDING 
AS
SELECT        Extent1.Id, Extent1.Name, Extent1.Ric, Extent1.LongName, Extent1.ParentId, Extent1.Isin, Extent1.Ticker, Extent2.Name AS Class, Extent3.EconomicSectorDescription AS Sector, 
                         Extent4.CountryDescription AS Country, Extent5.Name AS Currency
FROM            MarketData.[Security] AS Extent1 INNER JOIN
                         MarketData.SecurityClass AS Extent2 ON Extent1.ClassId = Extent2.Id INNER JOIN
                         MarketData.EconomicSector AS Extent3 ON Extent1.EconomicSectorId = Extent3.Id INNER JOIN
                         MarketData.Country AS Extent4 ON Extent1.CountryId = Extent4.Id INNER JOIN
                         MarketData.Currency AS Extent5 ON Extent1.CurrencyId = Extent5.Id
GO

PRINT N'CREATE View [MarketData].[SecurityView] FINISH'
GO

----##########################################################################################################################################################################
PRINT N'CREATE View [MarketData].[UsedSecurities] START'
GO
/****** Object:  View [MarketData].[UsedSecurities]    Script Date: 5/21/2014 11:24:43 AM ******/
SET ANSI_NULLS ON
SET ANSI_PADDING ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [MarketData].[UsedSecurities]
WITH SCHEMABINDING
AS
SELECT     s.Id, s.Isin, s.Ric, s.Mic, s.Sedol, s.CustomIdentificator, s.Name, s.LongName, s.ClassId, s.CurrencyId, s.EconomicSectorId, s.CountryId, s.ParentId, s.ParentIdentify, s.ParentIdentifyType, s.Type, s.Ticker
FROM       MarketData.Security AS s  INNER JOIN
           MarketData.SecurityClass AS sc ON s.ClassId = sc.Id
WHERE      sc.Include = 1
GO

PRINT N'CREATE View [MarketData].[UsedSecurities] FINISH'
GO
----##########################################################################################################################################################################